﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Runtime.InteropServices.ComTypes;
using System.Text;
using System.Threading.Tasks;

namespace UavForm
{
    public class DbHelper : IDisposable
    {
        private const string DbPath = "Data Source=Readings.db";
        private SQLiteConnection connection;

        public DbHelper()
        {
            connection = new SQLiteConnection(DbPath);
            connection.Open();
        }
        private SQLiteCommand CreateCommand(string text)
        {
            return new SQLiteCommand(text, connection);
        }

        public void Dispose()
        {
            if (connection?.State == ConnectionState.Open)
            {
                connection.Close();
            }
            connection?.Dispose();
        }

        public List<string> QueryDays(string sql)
        {
            var result = new List<string>();

            try
            {
                using (var cmd = CreateCommand(sql))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                            result.Add(reader[i] + "");
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                return new List<string>();
            }
        }

        public List<int> GetDistinctDays()
        {
            var days = new List<int>();
            const string sql = "SELECT DISTINCT Day FROM UAAVData";

            using (var cmd = CreateCommand(sql))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    days.Add(reader.GetInt32(0));
                }
            }
            return days;
        }

        public bool AddFlightData(UavModel data) 
        {
            const string sql = "INSERT INTO UAAVData "+
            "(Day, RunningTime, Speed, CabTemp, EngTemp, Fuel, Battery, ShockWear) " +
            "VALUES (@Day, @Runtime, @Speed, @CabTemp, @EngTemp, @Fuel, @Battery, @ShockWear)";
            try
            {
                using (var cmd = new SQLiteCommand(sql, connection))
                {
                    cmd.Parameters.AddWithValue("@Day", data.Day);
                    cmd.Parameters.AddWithValue("@Runtime", data.RunningTime);
                    cmd.Parameters.AddWithValue("@Speed", data.Speed);
                    cmd.Parameters.AddWithValue("@CabTemp", data.CabTemp);
                    cmd.Parameters.AddWithValue("@EngTemp", data.EngTemp);
                    cmd.Parameters.AddWithValue("@Fuel", data.Fuel);
                    cmd.Parameters.AddWithValue("@Battery", data.Battery);
                    cmd.Parameters.AddWithValue("@ShockWear", data.ShockWear);

                    return cmd.ExecuteNonQuery() > 0;
                }
            }
            catch
            {
                throw;
            }
        }

        public DataSet FetchData(string selectQuery)
        {
            using (var conn = new SQLiteConnection(connection))
            {
                try
                {
                    var adapter = new SQLiteDataAdapter(selectQuery, conn);
                    var ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }


       
    }
}